﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_seal_aut_calc')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_seal_aut_calc

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-25 09:45:54
-- Database:    [scm_main-hysj2]
-- Description: 考勤管理-考勤封账自动计算
-- =============================================

CREATE PROCEDURE bd_proc_seal_aut_calc
  @sealYear INT,
  @sealMonth  INT
AS
BEGIN
  SET NOCOUNT ON;

  --  年月、考勤状态、封账状态、工号、姓名、员工类型、所属公司、工作状态

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
    user_name VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
    dept_id BIGINT,
    dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
    seal_year INT,
    seal_month INT,
    seal_ym VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
    lock_status INT DEFAULT 0,
    attendance_status INT DEFAULT -1,
    should_hour INT DEFAULT 0,
    actual_hour INT DEFAULT 0,
    overtime_hour INT DEFAULT 0,
    off_duty_hour INT DEFAULT 0,
    leave_hour INT DEFAULT 0,
    late_hour INT DEFAULT 0,
    early_hour INT DEFAULT 0,
    late_count  INT DEFAULT 0,
    early_count INT DEFAULT 0,
    absent_count  INT DEFAULT 0
  )


  DECLARE @settleYm VARCHAR(10)
  SET @settleYm = CAST(@sealYear AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(@sealMonth AS VARCHAR(2)), 2) 

  INSERT INTO #result(user_serial, user_no, user_name, dept_id, dept_name, seal_year, seal_month, seal_ym, should_hour, actual_hour, 
    overtime_hour, off_duty_hour, leave_hour, late_hour, early_hour)
  SELECT card.user_serial, card.user_no, card.user_name, card.dept_id, card.dept_name, @sealYear, @sealMonth, @settleYm, 
    SUM(card.should_hours), SUM(card.actual_hours),
    SUM(card.ot_pending_hours), SUM(card.off_duty_hour), SUM(card.leave_hours_total) - SUM(card.off_duty_hour), SUM(card.late_hour), SUM(card.early_hour)
  FROM bd_kq_timecard card
  INNER JOIN dt_user u ON u.user_serial = card.user_serial
  WHERE settle_year = @sealYear AND settle_month = @sealMonth AND ISNULL(card.shift_id, 0) > 0 
  GROUP BY card.user_serial, card.user_no, card.user_name, card.dept_id, card.dept_name

  UPDATE #result SET leave_hour = 0 WHERE ISNULL(leave_hour, 0) < 0

  --不计算已经封账的
  DELETE FROM #result WHERE user_serial IN (SELECT user_serial FROM bd_kq_accounts WHERE [year] = @sealYear AND [month] = @sealMonth AND lock_status > 0)


  --迟到次数
  UPDATE r
  SET late_count = x.qty
  FROM #result r
  INNER JOIN (
      SELECT
        r.user_serial, COUNT(1) qty
      FROM #result r
    INNER JOIN bd_kq_timecard card ON card.user_serial = r.user_serial AND card.settle_year = r.seal_year AND card.settle_month = r.seal_month
      WHERE ISNULL(card.status, 0) = 4
      GROUP BY r.user_serial
    ) x ON x.user_serial = r.user_serial

  --早退次数
  UPDATE r
  SET late_count = x.qty
  FROM #result r
  INNER JOIN (
      SELECT
        r.user_serial, COUNT(1) qty
      FROM #result r
    INNER JOIN bd_kq_timecard card ON card.user_serial = r.user_serial AND card.settle_year = r.seal_year AND card.settle_month = r.seal_month
      WHERE ISNULL(card.status, 0) = 5
      GROUP BY r.user_serial
    ) x ON x.user_serial = r.user_serial  

  --缺勤次数
  UPDATE r
  SET absent_count = x.qty
  FROM #result r
  INNER JOIN (
      SELECT
        r.user_serial, COUNT(1) qty
      FROM #result r
    INNER JOIN bd_kq_timecard card ON card.user_serial = r.user_serial AND card.settle_year = r.seal_year AND card.settle_month = r.seal_month
      WHERE ISNULL(card.status, 0) = 6
      GROUP BY r.user_serial
    ) x ON x.user_serial = r.user_serial  


  --考勤正常
  UPDATE r
  SET attendance_status = 1
  FROM #result r
  INNER JOIN (
      SELECT DISTINCT
        user_serial, status
      FROM bd_kq_timecard
      WHERE settle_year = @sealYear AND settle_month = @sealMonth AND status = 0
    ) x ON x.user_serial = r.user_serial

  --考勤异常
  UPDATE r
  SET attendance_status = 2
  FROM #result r
  INNER JOIN (
      SELECT DISTINCT
        user_serial, status
      FROM bd_kq_timecard
      WHERE settle_year = @sealYear AND settle_month = @sealMonth AND status >= 4
    ) x ON x.user_serial = r.user_serial



--SELECT * FROM #result WHERE user_serial=20010963

  BEGIN TRY
    BEGIN TRAN

      MERGE
      INTO bd_kq_accounts p
      USING #result s ON p.user_serial = s.user_serial
        AND p.year = s.seal_year
        AND p.month = s.seal_month
      WHEN MATCHED
        THEN UPDATE SET
            p.dept_id = s.dept_id
            , p.year = s.seal_year
            , p.month = s.seal_month
            , p.seal_ym = s.seal_ym
--            , p.lock_status = s.lock_status
            , p.attendance_status = s.attendance_status
            , p.should_hour = s.should_hour
            , p.actual_hour = s.actual_hour
            , p.overtime_hour = s.overtime_hour
            , p.off_duty_hour = s.off_duty_hour
            , p.leave_hour = s.leave_hour
            , p.late_hour = s.late_hour
            , p.early_hour = s.early_hour
            , p.late_count = s.late_count
            , p.early_count = s.early_count
            , p.absent_count = s.absent_count
            , p.update_by = 99999
            , p.update_time = GETDATE()
      WHEN NOT MATCHED
        THEN INSERT(user_serial, dept_id, year, month, seal_ym, lock_status, attendance_status, should_hour, actual_hour, overtime_hour, off_duty_hour, leave_hour, late_hour, early_hour, create_by, create_time)
            VALUES(user_serial, dept_id, s.seal_year, s.seal_month, seal_ym, lock_status, attendance_status, should_hour, actual_hour, overtime_hour, off_duty_hour, leave_hour, late_hour, early_hour, 99999, GETDATE())
      ;

      SELECT 1

    COMMIT TRAN
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
      RAISERROR (@errStr, 16, 1);
      SELECT 0
    END;
  END CATCH 

  DROP TABLE #result

  SET NOCOUNT OFF;
END